import time
import xlwings as xw


# excel 的格式如下
# 微信号   备注    标签

# 定义一个空的对象表示行数据，使用的时候可以随意填充
class LineData:
    #验证信息
    validationMessage = None
    #微信号
    account = None
    #备注
    memo = None
    #标签
    tag = None


class ReadAccounts:
    __currentLine = 2
    __app = None
    __activeWorkbook = None
    __activeSheet = None

    # 迭代器，用来通过for in 来循环读取，具体参考
    # https://www.zhihu.com/question/24868112
    def __iter__(self):
        self.currentLine = 2
        return self

    def __next__(self):
        lineData = self.__readLine()
        if lineData == None or lineData.account == None or lineData.account == 'None':
            raise StopIteration()
        return lineData

    # 加载excel
    def __init__(self):
        pass

    def close(self):
        # 关闭工作簿
        if self.__activeWorkbook:
            self.__activeWorkbook.close()

        # 关闭excel
        if self.__app:
            self.__app.kill()

    # 加载excel
    def load(self, path):
        # 获取app对象
        if not xw.apps.active:
            self.__app = xw.App()
            self.__app.visible = False
            self.__app.display_alerts = False
            self.__app.screen_updating = False  # 加快脚本处理速度，但是看不到脚本的操作了。
        else:
            self.__app = xw.apps.active

        # 连接到excel
        if not self.__activeWorkbook:
            if self.__app.books:
                if len(self.__app.books) > 0:
                    self.__activeWorkbook = self.__openedWorkBook(path, self.__app)
                    if not self.__activeWorkbook:
                        self.__activeWorkbook = self.__openWorkBook(path, self.__app)
                    else:
                        # 激活工作簿
                        self.__activeWorkbook.activate()
                else:
                    self.__activeWorkbook = self.__openWorkBook(path, self.__app)
            else:
                self.__activeWorkbook = self.__openWorkBook(path, self.__app)

        # 找到 sheet
        if not self.__activeSheet:
            self.__activeSheet = self.__activeWorkbook.sheets.active

    def __readLine(self):
        if self.__activeSheet:
            lineData = LineData()
            lineData.account = self.__activeSheet.range('A' + str(self.__currentLine)).value
            lineData.memo = self.__activeSheet.range('B' + str(self.__currentLine)).value
            lineData.tag = self.__activeSheet.range('C' + str(self.__currentLine)).value
            lineData.validationMessage = self.__activeSheet.range('D' + str(self.__currentLine)).value
            # 去掉第一个字符（`）
            if lineData.account is not None and lineData.account[0] == '`':
                lineData.account = lineData.account[1:len(lineData.account)]
            if lineData.memo is not None and lineData.memo[0] == '`':
                lineData.memo = lineData.memo[1:len(lineData.memo)]
            if lineData.tag is not None and lineData.tag[0] == '`':
                lineData.tag = lineData.tag[1:len(lineData.tag)]
            if lineData.validationMessage is not None and lineData.validationMessage[0] == '`':
                lineData.validationMessage = lineData.validationMessage[1:len(lineData.validationMessage)]
            self.__currentLine += 1
            return lineData
        return None

    # 判断工作簿是否打开
    def __openedWorkBook(self, path, app):
        for book in app.books:
            # 检查是否打开了需要访问的文件
            if book.fullname == path:
                return book
        return None

    # 在excel中打开工作簿
    def __openWorkBook(self, path, app):
        workBook = app.books.open(path)  # 连接excel文件
        # 等待5秒确保excel已经准备好
        # time.sleep(5)
        # 激活工作簿
        workBook.activate()
        return workBook
